#include "bootuser.h"
#include "ui_bootuser.h"

bootuser::bootuser(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::bootuser)
{
    ui->setupUi(this);

    QTimer *timer = new QTimer(this);
    connect(timer,SIGNAL(timeout()),this,SLOT(timeUpdate()));
    timer->start(1000);//计时更新

    QDate DFD(2021,3,1);//规定学期第一天
    QDate CD = QDate::currentDate();
    int diff = (DFD.daysTo(CD))/7+1; //当前周
    ui->CurrentWeekNumber->setNum(diff);
//数据库连接准备
    db = QSqlDatabase::addDatabase("QMYSQL");
    db.setHostName("127.0.0.1");
    db.setPort(3306);
    db.setDatabaseName("experimentdb");
    db.setUserName("root");
    db.setPassword("1234");
    if(db.open()){
        qDebug("boot_SQL connect success!");
    }else{
        qDebug("boot_SQL connect failed!");
    }
//课程信息数据源设定
    course_model = new QSqlTableModel(this);
    course_model->setTable("course_message");
    course_model->setEditStrategy(QSqlTableModel::OnManualSubmit);//设置提交策略，手动提交
//教师信息数据源设定
    Teacher_model = new QSqlTableModel(this);
    Teacher_model->setTable("account");
    Teacher_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
//学生信息数据源设定
    Student_model = new QSqlTableModel(this);
    Student_model->setTable("account");
    Student_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
//实验时间数据源设定
    ExpTime_model = new QSqlTableModel(this);
    ExpTime_model->setTable("exp_time");
    ExpTime_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
//班级信息数据源设定
    Class_model = new QSqlTableModel(this);
    Class_model->setTable("class_message");
    Class_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
//预约实验数据源设定
    Exp_model = new QSqlTableModel(this);
    Exp_model->setTable("exp_reservation");
    Exp_model->setEditStrategy(QSqlTableModel::OnManualSubmit);
}

bootuser::~bootuser()
{
    delete ui;
}
//时间显示
void bootuser::timeUpdate(void)
{
    QDateTime time = QDateTime::currentDateTime();
    QString str = time.toString("yyyy-MM-dd hh:mm:ss dddd");
    ui->CurrentDateTime->setText(str);
}
//添加实验课程信息
void bootuser::on_Course_Add_clicked()
{
    record = course_model->record();
    row = course_model->rowCount();//新建一行来增加数据
    course_model->insertRecord(row,record);
}
//查询所有实验课程信息并显示
void bootuser::on_Course_Search_All_clicked()
{
    ui->Course_Show->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    course_model->setFilter(nullptr);
    course_model->select();//选中全部数据
    course_model->setHeaderData(0,Qt::Horizontal,tr("课程名"));
    course_model->setHeaderData(1,Qt::Horizontal,tr("课程号"));
    ui->Course_Show->setModel(course_model);
}
//删除实验课程信息
void bootuser::on_Course_Del_clicked()
{
    select = ui->Course_Show->selectionModel();
    QModelIndexList list =select->selectedRows();
    for(int i=0;i<list.size();i++){//可删除一行或多行数据
        course_model->removeRow(list.at(i).row());
    }
}
//查询指定的课程信息
void bootuser::on_Course_Search_clicked()
{
    uiCourseName = ui->Course_Name->text();
    uiCourseID = ui->Course_ID->text();
    if(uiCourseName == nullptr && uiCourseID == nullptr){
        QMessageBox::warning(this,"提示","请输入需要查询的信息！");
    }else if(uiCourseName != nullptr){
        cmd = QString("course_name ='%1'").arg(uiCourseName);
        course_model->setFilter(cmd);
        course_model->select();
    }else{
        cmd = QString("course_id ='%1'").arg(uiCourseID);
        course_model->setFilter(cmd);
        course_model->select();
    }
}
//课程：提交修改或者添加的信息
void bootuser::on_Course_Save_clicked()
{
    course_model->submitAll();
    if(!course_model->submitAll()){
        QMessageBox::critical(this,"提示","已有该课程号！");
    }else {
        QMessageBox::information(this,"提示","操作成功！");
    }
}
//课程：撤销正在进行的操作
void bootuser::on_Course_Cancel_clicked()
{
    course_model->revertAll();
    course_model->submitAll();
}
//查询所有注册教师用户信息并显示
void bootuser::on_Teacher_Search_All_clicked()
{
    ui->Teacher_Show->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    Teacher_model->setFilter(QString("account_identity ='教师'"));
    Teacher_model->select();
    Teacher_model->setHeaderData(0,Qt::Horizontal,tr("用户姓名"));
    Teacher_model->setHeaderData(1,Qt::Horizontal,tr("用户工号"));
    Teacher_model->setHeaderData(2,Qt::Horizontal,tr("用户密码"));
    Teacher_model->setHeaderData(3,Qt::Horizontal,tr("用户身份"));
    ui->Teacher_Show->setModel(Teacher_model);
}
//查询指定的教师信息
void bootuser::on_Teacher_Search_clicked()
{
    uiName = ui->Teacher_Name->text();
    uiID = ui->Teacher_ID->text();
    if(uiName == nullptr && uiID == nullptr){
        QMessageBox::warning(this,"提示","请输入需要查询的信息！");
    }else if(uiName != nullptr){
        cmd = QString("account_name ='%1'").arg(uiName);
        Teacher_model->setFilter(cmd);
        Teacher_model->select();
    }else{
        cmd = QString("account_id ='%1'").arg(uiID);
        Teacher_model->setFilter(cmd);
        Teacher_model->select();
    }
}
//添加教师用户
void bootuser::on_Teacher_Add_clicked()
{
    record = Teacher_model->record();
    row = Teacher_model->rowCount();//新建一行来增加数据
    Teacher_model->insertRecord(row,record);
}
//删除教师用户信息
void bootuser::on_Teacher_Del_clicked()
{
    select = ui->Teacher_Show->selectionModel();
    QModelIndexList list =select->selectedRows();
    for(int i=0;i<list.size();i++){//可删除一行或多行数据
        Teacher_model->removeRow(list.at(i).row());
    }
}
//教师：提交修改或者添加的信息
void bootuser::on_Teacher_Save_clicked()
{
    Teacher_model->submitAll();
    if(!Teacher_model->submitAll()){
        QMessageBox::critical(this,"提示","已有该工号！");
    }else {
        QMessageBox::information(this,"提示","操作成功！");
    }
}
//教师：撤销正在进行的操作
void bootuser::on_Teacher_Cancel_clicked()
{
    Teacher_model->revertAll();
    Teacher_model->submitAll();
}
//查询所有注册学生用户信息并显示
void bootuser::on_Student_Search_All_clicked()
{
    ui->Student_Show->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    Student_model->setFilter(QString("account_identity ='学生'"));
    Student_model->select();
    Student_model->setHeaderData(0,Qt::Horizontal,tr("用户姓名"));
    Student_model->setHeaderData(1,Qt::Horizontal,tr("用户工号"));
    Student_model->setHeaderData(2,Qt::Horizontal,tr("用户密码"));
    Student_model->setHeaderData(3,Qt::Horizontal,tr("用户身份"));
    ui->Student_Show->setModel(Student_model);
}
//删除学生用户信息
void bootuser::on_Student_Del_clicked()
{
    select = ui->Student_Show->selectionModel();
    QModelIndexList list =select->selectedRows();
    for(int i=0;i<list.size();i++){//可删除一行或多行数据
        Student_model->removeRow(list.at(i).row());
    }
}
//添加学生用户
void bootuser::on_Student_Add_clicked()
{
    record = Student_model->record();
    row = Student_model->rowCount();//新建一行来增加数据
    Student_model->insertRecord(row,record);
}
//查询指定的学生信息
void bootuser::on_Student_Search_clicked()
{
    uiName = ui->Student_Name->text();
    uiID = ui->Student_ID->text();
    if(uiName == nullptr && uiID == nullptr){
        QMessageBox::warning(this,"提示","请输入需要查询的信息！");
    }else if(uiName != nullptr){
        cmd = QString("account_name ='%1'").arg(uiName);
        Student_model->setFilter(cmd);
        Student_model->select();
    }else{
        cmd = QString("account_id ='%1'").arg(uiID);
        Student_model->setFilter(cmd);
        Student_model->select();
    }
}
//学生：提交修改或者添加的信息
void bootuser::on_Student_Save_clicked()
{
    Student_model->submitAll();
    if(!Student_model->submitAll()){
        QMessageBox::critical(this,"提示","已有该工号！");
    }else {
        QMessageBox::information(this,"提示","操作成功！");
    }
}
//学生：撤销正在进行的操作
void bootuser::on_Student_Cancel_clicked()
{
    Student_model->revertAll();
    Student_model->submitAll();
}
//添加实验时间
void bootuser::on_Exp_Time_Add_clicked()
{
    uiExpWeek = ui->Exp_Week->currentText();
    uiExpTime = ui->Exp_Time->currentText();
    cmd = QString("select * from exp_time where exp_week ='%1' and exp_time ='%2'").arg(uiExpWeek).arg(uiExpTime);
    QSqlQuery query(cmd,db);
    while(query.next()){
        ExpWeek_db =query.value(0).toString();
        ExpTime_db = query.value(1).toString();
    }
    if(uiExpWeek == ExpWeek_db && uiExpTime == ExpTime_db){
        QMessageBox::critical(this,"提示","该时间段已有！");
    }else{
        cmd = QString("insert into exp_time(exp_week,exp_time) values('%1', '%2')").arg(uiExpWeek).arg(uiExpTime);
        QSqlQuery query(cmd,db);
        QMessageBox::information(this,"提示","添加成功！");
        ui->Exp_Time_Show->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
        ExpTime_model->setFilter(nullptr);
        ExpTime_model->select();//选中全部数据
        ExpTime_model->setHeaderData(0,Qt::Horizontal,tr("星期数"));
        ExpTime_model->setHeaderData(1,Qt::Horizontal,tr("时间段"));
        ui->Exp_Time_Show->setModel(ExpTime_model);
    }
}
//查询实验时间
void bootuser::on_Exp_Time_Search_All_clicked()
{
    ui->Exp_Time_Show->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    ExpTime_model->setFilter(nullptr);
    ExpTime_model->select();//选中全部数据
    ExpTime_model->setHeaderData(0,Qt::Horizontal,tr("星期数"));
    ExpTime_model->setHeaderData(1,Qt::Horizontal,tr("时间段"));
    ui->Exp_Time_Show->setModel(ExpTime_model);
}
//删除实验时间
void bootuser::on_Exp_Time_Del_clicked()
{
    select = ui->Exp_Time_Show->selectionModel();
    QModelIndexList list =select->selectedRows();
    for(int i=0;i<list.size();i++){//可删除一行或多行数据
        ExpTime_model->removeRow(list.at(i).row());
    }
}
//实验时间：提交修改的信息
void bootuser::on_Exp_Time_Save_clicked()
{
    ExpTime_model->submitAll();
    if(ExpTime_model->submitAll()){
        QMessageBox::information(this,"提示","操作成功！");
    }else{
        QMessageBox::critical(this,"提示","操作失败！");
    }
}
//实验时间：撤销当前操作
void bootuser::on_Exp_Time_Cancel_clicked()
{
    ExpTime_model->revertAll();
    ExpTime_model->submitAll();
}
//查询所有专业班级信息
void bootuser::on_Class_Search_All_clicked()
{
    ui->Class_Show->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    Class_model->setFilter(nullptr);
    Class_model->select();//选中全部数据
    Class_model->setHeaderData(0,Qt::Horizontal,tr("专业班级"));
    Class_model->setHeaderData(1,Qt::Horizontal,tr("班级人数"));
    ui->Class_Show->setModel(Class_model);
}
//查询指定的班级信息
void bootuser::on_Class_Search_clicked()
{
    uiClassName = ui->Class_Name->text();
    if(uiClassName == nullptr){
        QMessageBox::warning(this,"提示","请输入需要查询的班级！");
    }else{
        cmd = QString("class_name ='%1'").arg(uiClassName);
        Class_model->setFilter(cmd);
        Class_model->select();
    }
}
//添加班级信息
void bootuser::on_Class_Add_clicked()
{
    record = Class_model->record();
    row = Class_model->rowCount();//新建一行来增加数据
    Class_model->insertRecord(row,record);
}
//删除班级信息
void bootuser::on_Class_Del_clicked()
{
    select = ui->Class_Show->selectionModel();
    QModelIndexList list =select->selectedRows();
    for(int i=0;i<list.size();i++){//可删除一行或多行数据
        Class_model->removeRow(list.at(i).row());
    }
}
//班级：提交修改或者添加的信息
void bootuser::on_Class_Save_clicked()
{
    Class_model->submitAll();
    if(Class_model->submitAll()){
        QMessageBox::information(this,"提示","操作成功！");
    }else{
        QMessageBox::critical(this,"提示","已有该专业班级！");
    }
}
//班级：撤销当前操作
void bootuser::on_Class_Cancel_clicked()
{
    Class_model->revertAll();
    Class_model->submitAll();
}
//预约情况：查询所有
void bootuser::on_Apply_Search_All_clicked()
{
    ui->Exp_Apply_Show->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    Exp_model->setFilter(nullptr);
    Exp_model->select();
    Exp_model->setHeaderData(0,Qt::Horizontal,tr("周数"));
    Exp_model->setHeaderData(1,Qt::Horizontal,tr("星期"));
    Exp_model->setHeaderData(2,Qt::Horizontal,tr("时间段"));
    Exp_model->setHeaderData(3,Qt::Horizontal,tr("实验课程"));
    Exp_model->setHeaderData(4,Qt::Horizontal,tr("实验项目"));
    Exp_model->setHeaderData(5,Qt::Horizontal,tr("预约者"));
    Exp_model->setHeaderData(6,Qt::Horizontal,tr("实验班级"));
    Exp_model->setHeaderData(7,Qt::Horizontal,tr("班级人数"));
    ui->Exp_Apply_Show->setModel(Exp_model);
}
//查询指定条件的实验预约情况
void bootuser::on_Apply_Search_clicked()
{
    uiExpApply_WeekNumber = ui->Apply_Week_Number->currentText();
    uiExpApply_Week = ui->Apply_Week->currentText();
    if(uiExpApply_Week == "不限"){
        cmd = QString("周数 ='%1'").arg(uiExpApply_WeekNumber);
        Exp_model->setFilter(cmd);
        Exp_model->select();
    }else{
        cmd = QString("周数 ='%1' and 星期 ='%2'").arg(uiExpApply_WeekNumber).arg(uiExpApply_Week);
        Exp_model->setFilter(cmd);
        Exp_model->select();
    }
}
//增加实验预约
void bootuser::on_Apply_Add_clicked()
{
    record = Exp_model->record();
    row = Exp_model->rowCount();//新建一行来增加数据
    Exp_model->insertRecord(row,record);
}
//删除实验预约
void bootuser::on_Apply_Del_clicked()
{
    select = ui->Exp_Apply_Show->selectionModel();
    QModelIndexList list =select->selectedRows();
    for(int i=0;i<list.size();i++){//可删除一行或多行数据
        Exp_model->removeRow(list.at(i).row());
    }
}
//实验预约情况：提交修改或添加的信息
void bootuser::on_Apply_Save_clicked()
{
    Exp_model->submitAll();
    if(!Exp_model->submitAll()){
        QMessageBox::critical(this,"提示","操作失败！");
    }else {
        QMessageBox::information(this,"提示","操作成功！");
    }
}
//实验预约情况：撤销当前操作
void bootuser::on_Apply_Cancel_clicked()
{
    Exp_model->revertAll();
    Exp_model->submitAll();
}
//导出一周实验预约情况，导出为Excel格式
void bootuser::on_Apply_Output_clicked()
{
    uiExpApply_WeekNumber = ui->Apply_Week_Number->currentText();
    QXlsx::Document xlsx;
    QXlsx::Format title_format; /*设置标题的样式*/
    QXlsx::Format format2;/*小标题样式*/
    QXlsx::Format format3;/*数据内容样式*/
    title_format.setBorderStyle(QXlsx::Format::BorderThin);//外边框
    format2.setBorderStyle(QXlsx::Format::BorderThin);//外边框
    format3.setBorderStyle(QXlsx::Format::BorderThin);//外边框
    xlsx.setRowHeight(1,1,25);/*设置标题行高*/
    xlsx.setColumnWidth(1,8,20);/*设置列宽，一共5列参数*/

    title_format.setFontSize(11);
    title_format.setFontColor(QColor(Qt::red));
    title_format.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    title_format.setVerticalAlignment(QXlsx::Format::AlignVCenter);
    xlsx.mergeCells("A1:H1",title_format);//合并1~5列写入标题
    xlsx.write("A1","一周实验预约情况");

    format2.setFontColor(QColor(Qt::blue));
    format2.setHorizontalAlignment(QXlsx::Format::AlignHCenter);
    xlsx.write("A2", "周数", format2);
    xlsx.write("B2", "星期", format2);/*写入文字，应该刚才设置的样式*/
    xlsx.write("C2", "时间段", format2);
    xlsx.write("D2", "实验课程", format2);
    xlsx.write("E2", "实验项目", format2);
    xlsx.write("F2", "预约者", format2);
    xlsx.write("G2", "实验班级", format2);
    xlsx.write("H2", "班级人数", format2);

    format3.setHorizontalAlignment(QXlsx::Format::AlignHCenter);


    cmd = QString("select * from exp_reservation where 周数 ='%1'").arg(uiExpApply_WeekNumber);
    QSqlQuery query(cmd);
    int i=3;
    while(query.next())//一行一行遍历
    {
        xlsx.write(i,1,query.value(0).toString(),format3);
        xlsx.write(i,2,query.value(1).toString(),format3);
        xlsx.write(i,3,query.value(2).toString(),format3);
        xlsx.write(i,4,query.value(3).toString(),format3);
        xlsx.write(i,5,query.value(4).toString(),format3);
        xlsx.write(i,6,query.value(5).toString(),format3);
        xlsx.write(i,7,query.value(6).toString(),format3);
        xlsx.write(i,8,query.value(7).toString(),format3);
        i++;
    }
    //设置excel表格的默认文件名为"一周实验预约情况-当前时间"
    QString current_date =QDateTime::currentDateTime().toString(Qt::ISODate);
    QString fileName=tr("一周实验预约情况-")+current_date;
    QString dir=QString("../%1").arg(fileName);
    QString dir1=dir.replace(QRegExp(":"),"-");
    QString path = QFileDialog::getSaveFileName(this, tr("save"), dir1, "XLSX(*.xlsx)");
    xlsx.saveAs(path);/*保存*/
}
